<?php
/*
-- OVERVIEW
Class to do crud operations to data source 

-- Dependancies
 uses global $dbh for pdo connection
 
 -- Reccomend:
 - change to use Doctrine2 DBAL wrapper for database consistency
 - Fix Add to allow multiple inserts (consistent with Delete and Save)
 - fix Add to allow GUID insert for keys
     - if all keyvalues specified, use them  (KEEP AS IS)
	 - if 1 keyvalue missing: 
	    - if $sequence = 'GUID' then create and use GUID for entry key -- perhaps use pre-procesing function
 - (eventuall) Add Try/Catch error handling
 - Consider 

*/



class CF_CrudLibPdo {

/** structures used:
	* responce:
	* resonse['is_error'] - {true, false]
	* resonce['status'] - {ok, error}
	* responce['msg'] - text message about error
	* response['key'] [] - array of keys acted on.  same as $keyvalues, or for Adds, new keys added.
	
	newvalues
	
	keyvalues
	
*/


// end query in 'Where 1 '
static function CrudReadQuery($keyvalues,$query){
		global $dbh;
		if(is_array($keyvalues)){
		foreach($keyvalues AS $keyindex => $keys){
		// prepare where string from passed keys

			//$afterand = '';
			//$aftercomma = '';		
			foreach($keys AS $col_name => $col_value){
				//print "$col_name = $col_value <br />";
				$wherestr .= " AND " . TABLE_L_QUOTE . $col_name . TABLE_R_QUOTE . "=:KY$col_name ";
				//$afterand = ' AND ';
			}

			$sth = $dbh->prepare($query . $wherestr);	
			
			foreach($keys AS $col_name => $col_value){
				$bparam = ':KY' . $col_name; // NEED to escape this colname to take out any non-normal characters
				$sth->bindValue($bparam ,$col_value);
				//print "Binding Key $bparam TO $col_value";
			}			

			$is_ok = $sth->execute();
			
			$result = $sth->fetchall(PDO::FETCH_ASSOC);
			return $result;
			
		}
	}
}


// Note - since these operations are on only the one table, no need for table.column syntax ever.
	static function CrudSave($keyvalues, $newvalues, $maintable){
		global $dbh;
		if(is_array($keyvalues)){

		foreach($keyvalues AS $keyindex => $keys){
		// prepare where string from passed keys

			$afterand = '';
			$aftercomma = '';		
			foreach($keys AS $col_name => $col_value){
				//print "$col_name = $col_value <br />";
				$wherestr .= $afterand . " " . TABLE_L_QUOTE . $col_name . TABLE_R_QUOTE . "=:KY$col_name ";
				$afterand = ' AND ';
			}

			if(is_array($newvalues[$keyindex])){
			foreach($newvalues[$keyindex] AS $val_name => $val_value){
				$setstr .= $aftercomma . " " . TABLE_L_QUOTE . $val_name . TABLE_R_QUOTE . "=:ST$val_name  ";
				$aftercomma = ',';
			}
			}		

			$thetable =  $maintable;
			// Create prepared statement
			$qry = "UPDATE " . TABLE_L_QUOTE . $thetable . TABLE_R_QUOTE . " SET $setstr  WHERE ( " . $wherestr . " )";
			$sth = $dbh->prepare($qry);	
			// bind variables to passed keys	
			foreach($keys AS $col_name => $col_value){
				$bparam = ':KY' . $col_name; // NEED to escape this colname to take out any non-normal characters
				$sth->bindValue($bparam ,$col_value);
				//print "Binding Key $bparam TO $col_value";
			}
			// bind variables to passed set stmt
			if(is_array($keyvalues[$keyindex])){	
			foreach($newvalues[$keyindex] AS $val_name => $val_value){
				
				$bparam = ':ST' . $val_name; // NEED to escape this colname to take out any non-normal characters
				$sth->bindValue($bparam ,$val_value);
				//print "Biding NewValue $bparam TO $val_value ";
			}
			}		
			$is_ok = $sth->execute();
			$result['is_error'] = false;
			$result['status'] = 'ok';
			

			if(!$is_ok){
				$result['is_error'] = true;
				$result['status'] = 'error';
				$result['msg'] = $sth->errorInfo();
			}

	//	print 'Rowcount is ' . $result;		
			
			$setstr = '';
			$wherestr = '';	
			
			$result['keys'] = $keyvalues;
		}
		}
		return $result;
	}



	static function CrudDelete($keyvalues,$maintable){
		foreach($keyvalues AS $keyindex => $keys){
			//print "<br />";
			$afercomma = '';
			$afterand = '';
			foreach($keys AS $col_name => $col_value){
				//print "$col_name = $col_value <br />";
				$wherestr .= $afterand . " $col_name=:KY$col_name ";
				$afterand = ' AND ';
			}

		$qry = "DELETE FROM $maintable WHERE 1=0 OR ( " . $wherestr . " )";

		global $dbh;
		$stmt = $dbh->prepare($qry);	
		// bind variables to passed keys	
			foreach($keys AS $col_name => $col_value){
				$bparam = ':KY' . $col_name; // NEED to escape this colname to take out any non-normal characters
				$stmt->bindValue($bparam ,$col_value);
				//print "Binding $bparam TO $col_value";
			}	
			//$stmt->debugDumpParams();

			$is_good = $stmt->execute();
		if($is_good){
			$result['is_error'] = false;
			$result['status'] = 'ok';
			$result['keys'] = $keyvalues;			
		}else{	
				$result['is_error'] = true;
				$result['status'] = 'ok';
				$result['msg'] = $sth->errorInfo();
			}
		$wherestr = '';
		}

		
		return $result;
	}	
	
// WORKES ON ONLY 1 for now
// $addcolumns not used for now...
// This will add every column from new_values
// might add a mode:  if a key colum (sring column name) is passed, use autoincrement on that keyname.  If not, then insert to the key(s) in keyvalues
	static function CrudAdd($keyvalues,$newvalues,$maintable,$addcolumns = '', $sequence = null){

		if(is_array($keyvalues)){
			$has_needed_keys = true;
			foreach($keyvalues[0] AS $key_col => $key_value){
				if(empty($newvalues[0][$key_col]) AND $newvalues[0][$key_col] <> '0'){
					$has_needed_keys = false;
				}
			}			
			
		}else{
			if(!empty($keyvalues)){
				$sequence_key = $keyvalues;
			}else{
				$sequence_key = 'pk';
			}
		}
	

        foreach($newvalues[0] AS $col_name=>$col_value){
		// for input values, accept only non empty values.
            if(!empty($newvalues[0][$col_name]) OR $newvalues[0][$col_name] === '0'){
				$thisval = $newvalues[0][$col_name];
				($vars=='')?$vars=$col_name:$vars.=",$col_name";
				($vals=='')?$vals = ":" . $col_name . " ":$vals .= ", :" . $col_name . " ";
				
				// If we no data sent for this variable and it is a key, Dont include it, and let autoincrement catch it
            }
		}
        if($vars != ''){
            $qry_insert = "INSERT INTO $maintable ($vars) VALUES ($vals)";
			//print $qry_insert;
		
		global $dbh;
		$stmt = $dbh->prepare($qry_insert);	
        foreach($newvalues[0] AS $col_name => $colspec){
		// for input values, accept only non empty values.
		//print 'to binding: ' . $colname . ' = ' . $colspec . "\n";
	            if(!empty($newvalues[0][$col_name]) OR $newvalues[0][$col_name] === '0'){
				$thisval = $newvalues[0][$col_name];
				$bparam = ':' . $col_name; // NEED to escape this colname to take out any non-normal characters
				$stmt->bindValue($bparam ,$thisval);
				//print "Binding $thisval TO $bparam  ";
			}
		}			
		//	$stmt->debugDumpParams();
			
			
			    /*** fetch into an PDOStatement object ***/
				//$stmt = $dbh->prepare($qry_insert);

				/*** echo number of columns ***/
				$is_good = $stmt->execute();
				$result['is_error'] = false;
				$result['status'] = 'ok';
				
			if(!$is_good){	
				$result['is_error'] = true;
				$result['status'] = 'ok';
				$result['msg'] = $stmt->errorInfo();
			}else{
				if(!is_array($keyvalues)){
					if(!empty($sequence)){
					$thekeyval = $dbh->lastInsertID($sequence);
					}else{
					$thekeyval = $dbh->lastInsertID();
					}
					$result['keys'][0][$sequence_key] = $thekeyval;
				}else{
				$result['keys'] = $keyvalues;
				}
			}
			
			//catch(PDOException $e){	
			//	$result['keys'] = null;
			//}
		}	
		
		return $result;
	}
	
	
	
	
}

?>